﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class FlowRunFeedbackDao
    {
        public bool Insert(SqlConnection conn, FlowRunFeedbackData data)
        {
            string commandText = "IF NOT EXISTS(SELECT [RunId] FROM [dbo].[Flow_Run_Feedback] WHERE [RunId] = @RunId AND [PrcsId] = @PrcsId AND [UserId] = @UserId ) INSERT [dbo].[Flow_Run_Feedback] ( [RunId], [PrcsId], [FlowPrcs], [UserId], [Content], [AttachmentId], [EditTime] ) VALUES ( @RunId, @PrcsId, @FlowPrcs, @UserId, @Content, @AttachmentId, GETDATE() ) ELSE UPDATE [Flow_Run_Feedback] SET [Content] = @Content, [EditTime] = GETDATE() WHERE [RunId] = @RunId AND [PrcsId] = @PrcsId AND [UserId] = @UserId IF ISNULL(@AttachmentId,'') <> '' UPDATE [Flow_Run_Feedback] SET [AttachmentId] = @AttachmentId WHERE [RunId] = @RunId AND [UserId] = @UserId";
            SqlParameter[] commandParameters = new SqlParameter[7];
            commandParameters[0] = new SqlParameter("@RunId", SqlDbType.Int);
            commandParameters[0].Value = data.RunId;
            commandParameters[1] = new SqlParameter("@PrcsId", SqlDbType.Int);
            commandParameters[1].Value = data.PrcsId;
            commandParameters[2] = new SqlParameter("@FlowPrcs", SqlDbType.Int);
            commandParameters[2].Value = data.FlowPrcs;
            commandParameters[3] = new SqlParameter("@UserId", SqlDbType.NVarChar, 50);
            commandParameters[3].Value = data.UserId;
            commandParameters[4] = new SqlParameter("@Content", SqlDbType.NText);
            if (data.ContentIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Content;
            }
            commandParameters[5] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.AttachmentId;
            }
            commandParameters[6] = new SqlParameter("@EditTime", SqlDbType.DateTime);
            commandParameters[6].Value = data.EditTime;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public DataTable SelectByRunId(SqlConnection conn, int runId)
        {
            string commandText = "DECLARE @FlowType int SELECT @FlowType = [FlowType] FROM [dbo].[Flow_Type],[dbo].[Flow_Run] WHERE [Flow_Type].[FlowId] = [Flow_Run].[FlowId] AND [Flow_Run].[RunId] = @RunId IF @FlowType = 2 BEGIN SELECT [Flow_Run_Feedback].[PrcsId], [Flow_Run_Feedback].[FlowPrcs], [Flow_Run_Feedback].[UserId], [Flow_Run_Feedback].[AttachmentId], [Flow_Run_Feedback].[Content], [Flow_Run_Feedback].[EditTime], '' AS [PrcsName], [Department].[DeptName], [Users].[UserName] FROM [dbo].[Flow_Run_Feedback], [dbo].[Flow_Run], [dbo].[Department], [dbo].[Users] WHERE [Flow_Run_Feedback].[RunId] = @RunId AND [Flow_Run_Feedback].[RunId] = [Flow_Run].[RunId] AND [Flow_Run_Feedback].[UserId] = [Users].[UserId] AND [Users].[DeptId] = [Department].[DeptId] ORDER BY [Flow_Run_Feedback].[FeedbackId] ASC END ELSE BEGIN SELECT [Flow_Run_Feedback].[PrcsId], [Flow_Run_Feedback].[FlowPrcs], [Flow_Run_Feedback].[UserId], [Flow_Run_Feedback].[AttachmentId], [Flow_Run_Feedback].[Content], [Flow_Run_Feedback].[EditTime], [Flow_Process].[PrcsName], [Department].[DeptName], [Users].[UserName] FROM [dbo].[Flow_Run_Feedback], [dbo].[Flow_Run], [dbo].[Flow_Process], [dbo].[Department], [dbo].[Users] WHERE [Flow_Run_Feedback].[RunId] = @RunId AND [Flow_Run_Feedback].[RunId] = [Flow_Run].[RunId] AND [Flow_Run].[FlowId] = [Flow_Process].[FlowId] AND [Flow_Run_Feedback].[FlowPrcs] = [Flow_Process].[PrcsId] AND [Flow_Run_Feedback].[UserId] = [Users].[UserId] AND [Users].[DeptId] = [Department].[DeptId] ORDER BY [Flow_Run_Feedback].[FeedbackId] ASC END";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@RunId", SqlDbType.Int) };
            commandParameters[0].Value = runId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Run_Feedback";
            return set.Tables[0];
        }

        public DataTable SelectHistory(SqlConnection conn, string userId)
        {
            string commandText = "SELECT TOP 20 [FeedbackId],[Content] FROM [dbo].[Flow_Run_Feedback] WHERE [UserId] = @UserId ORDER BY [FeedbackId] DESC";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Run_Feedback";
            return set.Tables[0];
        }

        public DataTable SelectMy(SqlConnection conn, int runId, string userId, int prcsId)
        {
            string commandText = "SELECT [Content], [AttachmentId], [EditTime] FROM [dbo].[Flow_Run_Feedback] WHERE [RunId] = @RunId AND [UserId] = @UserId AND [PrcsId] = @PrcsId";
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@RunId", SqlDbType.Int);
            commandParameters[0].Value = runId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            commandParameters[2] = new SqlParameter("@PrcsId", SqlDbType.Int);
            commandParameters[2].Value = prcsId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Flow_Run_Feedback";
            return set.Tables[0];
        }
    }
}

